//连接数据库需要安装node连接数据库的插件 npm i mysql
const mysql = require('mysql')
const config = require('./defaultConfig')//把连接文件引入

//创建线程池(连接数据库的准备工作)
let pool = mysql.createPool({
    host: config.dataBase.HOST,
    user:config.dataBase.USERNAME,
    password:config.dataBase.PASSWORD,
    database:config.dataBase.DATABASE,
    port:config.dataBase.PORT,
})
//连接线程池，做SQL操作
let allService = {
    query: function(sql,value){//sql语句，value要操作的值
        return new Promise((resolve,reject) =>{
            pool.getConnection(function(err,connection){//连接线程池
                if(err){//连接失败
                    reject(err)
                } else {//连接成功
                    connection.query(sql,value,(err,rows) =>{
                        if(err){//sql语句操作失败
                            reject(err)
                        }else{//操作成功
                            resolve(rows)
                        }
                        connection.release()//释放连接
                    })
                }
            })
        })
    }
}

//用户登录

let userlogin = function(username, password){
    let _sql = `select * from users where username="${username}" and userpwd="${password}";`//选中user表,查找语句
    return allService.query(_sql)
}


//查找用户

let findUser = function(username){
    let _sql = `select * from users where username="${username}";`
    return allService.query(_sql)
}

// 用户注册

let insertUser = function(value){
    let _sql = `insert into users set username=?,userpwd=?,nickname=?;`
    return allService.query(_sql,value)
}

//根据类型查找对应的文章列表
let findfindNoteListByType = function(type,id){
    let _sql = `select * from note where useId="${id}" and note_type="${type}";`
    return allService.query(_sql)
}
//根据ID查找文章详情
let findNoteDetailById = function(id){
    let _sql = `select * from note where id="${id}";`
    return allService.query(_sql)
}
//把信息添加进数据库
let insertdiary = function(value){
    let _sql = `insert into note set note_content=?,title=?,head_img=?,note_type=?,nickname=?,useId=?,c_time=?,m_time=?;`
    return allService.query(_sql,value)
}
//修改数据库
let revisevtn = function (title,value,data){
    // console.log(title,value,data);
    var updateSql=`update note set note_content = "${value}",m_time = "${data}" where title = "${title}"`
    return allService.query(updateSql)
}
let deletebtn = function (note_content,userid){
    var updateSql=`delete from note where note_content = "${note_content}" and useId="${userid}"`
    return allService.query(updateSql)
}
module.exports = {
    userlogin,
    findUser,
    insertUser,
    findfindNoteListByType,
    findNoteDetailById,
    insertdiary,
    revisevtn,
    deletebtn
}